package com.cai.dao.impl;

import com.cai.dao.UtilsDao;
import com.cai.entity.Items;
import com.cai.entity.User;
import com.cai.util.C3p0Util;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * @author RuoLi
 */
public class UtilsDaoImpl implements UtilsDao {
    static Connection connection;
    static PreparedStatement preparedStatement;
    static ResultSet resultSet;
    static QueryRunner runner = new QueryRunner(C3p0Util.getDataSource());
    String sqlSelectItemType = "SELECT sum(item_num) FROM items  WHERE item_type=?";

    @Override
    public List<Items> itemQueryPage(int startRow, int pageSize) {
        String sql = "select * from items limit ?,?";
        return (List<Items>) queryPage(sql, startRow, pageSize, Items.class);
    }

    @Override
    public List<User> userQueryPage(int startRow, int pageSize) {
        String sql = "select * from user limit ?,?";
        return (List<User>) queryPage(sql, startRow, pageSize, User.class);
    }

    @Override
    public int selectAllPersonCount() {
        String sql = "select count(*) from user";
        return selectCount(sql);
    }

    @Override
    public int selectAllItemsCount() {
        String sql = "select count(*) from items";
        return selectCount(sql);
    }

    @Override
    public int selectPersonCount(String typeName) {
        String sql = "select count(*) from user where user_type=?";
        return selectCount(sql, new String[]{typeName});
    }

    @Override
    public int selectItemCount(String name) {
        int count;
        String[] item = {"总数", "借出", "归还"};
        if (name.equals(item[1])) {
            String sql = "SELECT sum(item_send) FROM items ";
            count = selectCount(sql);
        } else if (item[2].equals(name)) {
            String sql = "SELECT sum(item_return) FROM items ";
            count = selectCount(sql);
        } else if (item[0].equals(name)) {
            String sql = "SELECT sum(item_num) FROM items ";
            count = selectCount(sql);
        } else {
            return -1;
        }
        return count;
    }

    @Override
    public int selectItemType(String name) {
        int count1;
        String[] item = {"计量器具", "电器", "其他器具", "容器"};
        if (name.equals(item[0])) {
            count1 = selectCount(sqlSelectItemType, new String[]{item[0]});
        } else if (item[1].equals(name)) {
            count1 = selectCount(sqlSelectItemType, new String[]{item[1]});
        } else if (item[2].equals(name)) {
            count1 = selectCount(sqlSelectItemType, new String[]{item[2]});
        } else if (item[3].equals(name)) {
            count1 = selectCount(sqlSelectItemType, new String[]{item[3]});
        } else {
            return -1;
        }
        return count1;
    }

    @Override
    public int selectOnlineCount() {
        return 0;
    }

    @Override
   public List<?> queryPage(String sql, int startRow, int pageSize, Class<?> type) {
        List<?> list = new ArrayList<>();
        int page;
        try {
            if ((pageSize * (startRow - 1)) <= 0) {
                page = 0;
            } else {
                page = (pageSize * (startRow - 1));
            }
            Object[] objects = {page, pageSize};
            list = runner.query(sql, objects, new BeanListHandler<>(type));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
        try {
            if (connection != null) {
                connection.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 执行sql方法 查询
     *
     * @param sql sql语句
     * @return 对应的数据
     */
    public static int selectCount(String sql) {
        int count = 0;
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultSet);
        }
        return count;
    }

    /**
     * 单个数据查询
     *
     * @param sql    sql语句
     * @param params 参数
     * @return 数量
     */
    public static int selectCount(String sql, String[] params) {
        int length = params.length;
        int count = 0;
        try {
            connection = C3p0Util.getDataSource().getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i <= length; i++) {
                preparedStatement.setString(i, params[i-1]);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultSet);
        }
        return count;
    }

}
